#it is not easy to import one controller fns in another controllers; but its easy to import modules
#db instance is not visible to modules; so db should be explicitly passed

def index(): return dict(message="hello from api.py")

def get_priority_values(db):
	priority_values={}
	for priority in db().select(db.priority.ALL, orderby=db.priority.sort_order):
		priority_values[priority['id']]=priority['name']
	return priority_values

def get_status_values(db):
	status_values={}
	for status in db().select(db.status.ALL, orderby=db.status.sort_order):
		status_values[status['id']]=status['name']
	return status_values
    
def get_default_status(db): return db().select(db.status.ALL, orderby=db.status.sort_order)[0]['name']
def get_default_status_id(db): return db().select(db.status.ALL, orderby=db.status.sort_order)[0]['id']
def get_default_priority(db): return db().select(db.priority.ALL, orderby=db.priority.sort_order)[0]['name']
def get_default_priority_id(db): return db().select(db.priority.ALL, orderby=db.priority.sort_order)[0]['id']

def create_case(db,
		   title,
		   desc,
		   status,
		   priority,
		   created_via,
		   created_by,
		   created_at,
		   last_modified_by,
		   last_modified_at):
	db.cases.insert(title=title, 
            status=status, 
            priority=priority, 
            created_via=created_via,
            description=desc,
            created_by=created_by, 
            last_modified_by=last_modified_by, 
            created_time=created_at,
            last_modified_time=last_modified_at)
	
def get_cases(db, filter):
	#since GAE doesn't allow joins, here the final dict is built; otherwise it would've been a simple sql query output
	cases=[]
 	priority_values=get_priority_values(db)
 	status_values=get_status_values(db)
 	default_priority=get_default_priority(db)
 	default_status=get_default_status(db)
 

	for case in db(filter).select(db.cases.ALL):
		cases.append(dict(
        	id=case.id,
            title=case.title,
        	description=case.description,
        	priority=priority_values[case.priority]  if case.priority else default_priority,
        	priority_id=case.priority,
        	status=status_values[case.status] if case.status else default_status,
        	status_id=case.status,
        	assigned_to=db(db.auth_user.id==case.assigned_to).select(db.auth_user.ALL)[0].email if case.assigned_to else None,
        	created_by=db(db.auth_user.id==case.created_by).select(db.auth_user.ALL)[0].email,
        	created_time=str(case.created_time),
        	last_modified_by=db(db.auth_user.id==case.last_modified_by).select(db.auth_user.ALL)[0].email,
        	last_modified_time=str(case.last_modified_time)     
        	))
	return cases

def get_all_cases(db):
	return get_cases(db=db, filter='')
	
def get_case_details(db, case_id):
	case_detail=get_cases(db=db, filter=db.cases.id==case_id)
	return case_detail[0]
	
def add_notes(db, case_id, author, body, created_via,created_time):
	db.notes.insert(author=author, body=body, created_via=created_via,created_time=created_time, case_id=case_id)

def get_notes_for_case(db, case_id):
	return db(db.notes.case_id==case_id).select(orderby=~db.notes.created_time)

def get_agents(db):
	agent_group_id = db(db.auth_group.role=='agents').select(db.auth_group.ALL)[0].id
	agents_id=[agents_id['user_id'] for agents_id in db(db.auth_membership.group_id==agent_group_id).select(db.auth_membership.ALL)]
	agents=[]
	for agent in agents_id:
		agents.append(db(db.auth_user.id==agent).select(db.auth_user.ALL)[0].email)
	return agents

def get_user_id(db, user_email):
	return db(db.auth_user.email==user_email).select(db.auth_user.ALL)[0].id

def assign_case(db, case_value):
	#case_value is a dict
	ret_dict={}
	if (type(case_value) == dict and case_value.has_key('id')):
		#update_clause=",".join(str(k)+ '=' + str(v) for k,v in case_values.iteritems())
		try:
			db(db.cases.id==case_value['id']).update(assigned_to=get_user_id(db,case_value['assign_to']))
			ret_dict = dict(id=0, msg='Success')
		except Exception, e:
			ret_dict=dict(id=1, msg=str(e))
	else:
		ret_dict=dict(id=1, msg='Input values not in proper format')
	return ret_dict
		